﻿Imports System.Data
Imports System.Data.OleDb
Imports geraLaudosAutomatico.conexao
Imports System.Text

Public Class itens_avaliacaoDAO

    Dim sql As New StringBuilder
    Dim i As Integer
    Dim itens_avaliacaoVO As New itens_avaliacaoVO


    Public Function insereRegistro(ByVal grid As DataGridView, ByVal uc As ucPreambulo) As List(Of Integer)

        Dim numReg As Integer = 0
        Dim lista As New List(Of Integer)

        sql.Clear()
        sql.Append("INSERT INTO ITENS_AVALIACAO (NUM_REGISTRO,ANO_REGISTRO,ID_SECAO,DESC_ITEM,QTE,VALOR)")
        sql.Append(" VALUES ")
        sql.Append("(@NUM_REGISTRO,@ANO_REGISTRO,@ID_SECAO,@DESC_ITEM,@QTE,@VALOR)")

        Try


            For Each row As DataGridViewRow In grid.Rows

                Dim objConn As New conexao
                Dim oComando As New OleDbCommand(sql.ToString, objConn.abreConexao)

                oComando.Parameters.AddWithValue("@NUM_REGISTRO", uc.getNumeroLaudo)
                oComando.Parameters.AddWithValue("@ANO_REGISTRO", uc.getAno)
                oComando.Parameters.AddWithValue("@ID_SECAO", Login.Usuario.USUARIOLOGADO.SECAO.ID_SECAO)
                oComando.Parameters.AddWithValue("@DESC_ITEM", row.Cells(1).Value)
                oComando.Parameters.AddWithValue("@QTE", row.Cells(0).Value)
                oComando.Parameters.AddWithValue("@VALOR", row.Cells(2).Value)

                i = oComando.ExecuteNonQuery

                lista.Add(i)
            Next

        Catch ex As Exception

            generica.gravaErro(ex, "ERRO AO TENTAR GRAVAR OS ITENS DA LISTA NO BANCO DE DADOS LOCAL")

        End Try

        Return lista

    End Function


    Public Function getItens(ByVal num_registro As Integer, ByVal ano As Integer, ByVal id_secao As Integer) As Generic.List(Of itens_avaliacaoVO)

        sql.Clear()

        sql.Append("SELECT *FROM ITENS_AVALIACAO WHERE NUM_REGISTRO=@NUM_REGISTRO AND ANO_REGISTRO=@ANO_REGISTRO AND ID_SECAO=@ID_SECAO ORDER BY ID_ITEM")

        Dim oReader As OleDbDataReader

        Dim lista As New Generic.List(Of itens_avaliacaoVO)

        Dim objConn As New conexao

        Dim itens As New itens_avaliacaoVO


        Try
            Dim oComando As New OleDbCommand(sql.ToString, objConn.abreConexao)

            oComando.Parameters.AddWithValue("@NUM_REGISTRO", num_registro)
            oComando.Parameters.AddWithValue("@ANO_REGISTRO", ano)
            oComando.Parameters.AddWithValue("@ID_SECAO", id_secao)


            oReader = oComando.ExecuteReader

            While oReader.Read

                itens = New itens_avaliacaoVO
                itens.ID_ITEM = IIf(IsDBNull(oReader("ID_ITEM")), Nothing, oReader("ID_ITEM"))
                itens.NUM_REGISTRO = IIf(IsDBNull(oReader("NUM_REGISTRO")), Nothing, oReader("NUM_REGISTRO"))
                itens.ANO_REGISTRO = IIf(IsDBNull(oReader("ANO_REGISTRO")), Nothing, oReader("ANO_REGISTRO"))
                itens.ID_SECAO = IIf(IsDBNull(oReader("ID_SECAO")), Nothing, oReader("ID_SECAO"))
                itens.DESC_ITEM = IIf(IsDBNull(oReader("DESC_ITEM")), Nothing, oReader("DESC_ITEM"))
                itens.QTE = IIf(IsDBNull(oReader("QTE")), Nothing, oReader("QTE"))
                itens.VALOR = IIf(IsDBNull(oReader("VALOR")), Nothing, oReader("VALOR"))
                ' itens.DIRETORIO = IIf(IsDBNull(oReader("DIRETORIO")), Nothing, oReader("DIRETORIO"))
                lista.Add(itens)

            End While
        Catch ex As Exception

            generica.gravaErro(ex)

        Finally

            objConn.fechaConexao()

        End Try
        Return lista
    End Function


    Public Function getItemDiretorio(ByVal num_registro As Integer, ByVal ano As Integer, ByVal id_secao As Integer) As itens_avaliacaoVO

        sql.Clear()

        sql.Append("SELECT *FROM ITENS_AVALIACAO WHERE NUM_REGISTRO=@NUM_REGISTRO AND ANO_REGISTRO=@ANO_REGISTRO AND ID_SECAO=@ID_SECAO ORDER BY ID_ITEM")

        Dim oReader As OleDbDataReader

        ' Dim lista As New Generic.List(Of itens_avaliacaoVO)
        Dim diretorio As New itens_avaliacaoVO

        Dim objConn As New conexao

        Dim itens As New itens_avaliacaoVO


        Try
            Dim oComando As New OleDbCommand(sql.ToString, objConn.abreConexao(True))

            oComando.Parameters.AddWithValue("@NUM_REGISTRO", num_registro)
            oComando.Parameters.AddWithValue("@ANO_REGISTRO", ano)
            oComando.Parameters.AddWithValue("@ID_SECAO", id_secao)


            oReader = oComando.ExecuteReader

            While oReader.Read

                itens = New itens_avaliacaoVO
                itens.ID_ITEM = IIf(IsDBNull(oReader("ID_ITEM")), Nothing, oReader("ID_ITEM"))
                itens.NUM_REGISTRO = IIf(IsDBNull(oReader("NUM_REGISTRO")), Nothing, oReader("NUM_REGISTRO"))
                itens.ANO_REGISTRO = IIf(IsDBNull(oReader("ANO_REGISTRO")), Nothing, oReader("ANO_REGISTRO"))
                itens.ID_SECAO = IIf(IsDBNull(oReader("ID_SECAO")), Nothing, oReader("ID_SECAO"))
                itens.DESC_ITEM = IIf(IsDBNull(oReader("DESC_ITEM")), Nothing, oReader("DESC_ITEM"))
                itens.QTE = IIf(IsDBNull(oReader("QTE")), Nothing, oReader("QTE"))
                itens.VALOR = IIf(IsDBNull(oReader("VALOR")), Nothing, oReader("VALOR"))
                itens.DIRETORIO = IIf(IsDBNull(oReader("DIRETORIO")), Nothing, oReader("DIRETORIO"))
                diretorio = itens
                Exit While
            End While
        Catch ex As Exception

            generica.gravaErro(ex)

        Finally

            objConn.fechaConexao()

        End Try
        Return diretorio
    End Function


End Class
